package com.project.dao.impl;

import com.project.bean.ChannelBean;
import com.project.bean.CutPageBean;
import com.project.bean.PostBean;
import com.project.bean.UserBean;
import com.project.dao.IChannelDao;
import com.project.dao.PostDao;
import com.project.servic.IPostService;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
@Repository
public class PostDaoImpl extends BaseDao implements PostDao {
    @Override
    public CutPageBean<PostBean> findChannelById(Integer id ,Integer pageNo, String userName, String postName, LocalDate startDate, LocalDate endDate) {
        CutPageBean<PostBean> cut = new CutPageBean<>();
        this.setConnection();
        String sql = "SELECT s.*, p.`t_name` p,COUNT(r.`fk_postId`)replyNum FROM t_post s LEFT JOIN t_user p " +
                " ON p.`pk_userId`=s.`fk_userId` " +
                "   LEFT JOIN t_reply r ON s.`pk_postId`=r.`fk_postId` WHERE s.`fk_channelId`=? ";
        try {
            String dynaSql = "";
            if (postName != null && postName.length() != 0){
                dynaSql += "AND s.`t_name` LIKE '%"+postName+"%' ";
            }
            if (userName != null && userName.length() != 0){
                dynaSql += "AND p.`t_name` LIKE '%"+userName+"%' ";
            }
            if (startDate != null){
                dynaSql += "AND s.`t_date` >= '"+startDate+"' ";
            }
            if (endDate != null){
                dynaSql += "AND s.`t_date` <= '"+endDate+"' ";
            }
            ps= con.prepareStatement(sql+dynaSql+"GROUP BY s.`pk_postId` LIMIT ?,?");
            ps.setObject(1,id);
            ps.setObject(2,(pageNo-1)*CutPageBean.PAGESIZE);
            ps.setObject(3,CutPageBean.PAGESIZE);
            rs = ps.executeQuery();
            while (rs.next()){
                PostBean post = new PostBean();
                post.setId(rs.getInt("pk_postId"));
                post.setPostName(rs.getString("t_name"));
                post.setPostContent(rs.getString("t_content"));
                ChannelBean channelBean = new ChannelBean();
                channelBean.setId(rs.getInt("fk_channelId"));
                post.setChannel(channelBean);
                String bir = rs.getString("t_date");
                post.setDate(bir!=null? LocalDate.parse(bir):null);
                UserBean user = new UserBean();
                user.setUserName(rs.getString("p"));
                post.setPostWriter(user);
                post.setReplyNum(rs.getInt("replyNum"));
                cut.getList().add(post);
            }
            ps = con.prepareStatement("select  count(*) c from t_post s LEFT JOIN t_user p " +
                    "  ON p.`pk_userId`=s.`fk_userId` " +
                    "  WHERE s.`fk_channelId`=? "+dynaSql);
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                cut.setCount(rs.getInt("c"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }

        return cut;
    }

    @Override
    public void add(PostBean post) {
       this.setConnection();
        try {
            ps = con.prepareStatement("INSERT INTO t_post(t_name,t_content,t_date,fk_userId,fk_channelId) " +
                    "VALUES (?,?,?,?,?)");
            ps.setObject(1,post.getPostName());
            ps.setObject(2,post.getPostContent());
            ps.setObject(3,post.getDate());
            ps.setObject(4,post.getPostWriter().getId());
            ps.setObject(5,post.getChannel().getId());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }

    }

//    @Override
//    public CutPageBean<PostBean> findByItem(Integer pageNo) {
//        this.setConnection();
//        String dynaSql = " where 1=1 ";
//        String sql = "";
//        List valueList = new ArrayList<>();
//        if (name != null && name.length() != 0){
//            dynaSql += "and t_name like ? ";
//            valueList.add("%"+name+"%");
//        }
//        if (startDate != null){
//            dynaSql += "and p_createDate >= ? ";
//            valueList.add(startDate);
//        }
//        if (endDate != null){
//            dynaSql += "and p_createDate <= ? ";
//            valueList.add(endDate);
//        }
//        return null;
//    }

    @Override
    public List<PostBean> findByPostId(Integer id) {
        this.setConnection();
        List<PostBean> list = new ArrayList<>();
        try {
            ps = con.prepareStatement("SELECT s.*, p.`t_name` p,COUNT(r.`fk_postId`)replyNum FROM t_post s LEFT JOIN t_user p" +
                    "  ON p.`pk_userId`=s.`fk_userId`" +
                    "  LEFT JOIN t_reply r ON s.`pk_postId`=r.`fk_postId` WHERE s.`fk_channelId`=? GROUP BY s.`pk_postId`");
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                PostBean post = new PostBean();
                    post.setId(rs.getInt("pk_postId"));
                    post.setPostName(rs.getString("t_name"));
                    post.setPostContent(rs.getString("t_content"));
                    ChannelBean channelBean = new ChannelBean();
                    channelBean.setId(rs.getInt("fk_channelId"));
                    post.setChannel(channelBean);
                    String bir = rs.getString("t_date");
                    post.setDate(bir!=null? LocalDate.parse(bir):null);
                    UserBean user = new UserBean();
                    user.setUserName(rs.getString("p"));
                    post.setPostWriter(user);
                    post.setReplyNum(rs.getInt("replyNum"));
                    list.add(post);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public PostBean findById(Integer id) {
        this.setConnection();
        PostBean post = null;
        try {
            ps = con.prepareStatement("SELECT s.*, p.`t_name` p,count(r.`fk_postId`)replyNum FROM t_user p,t_post s,t_reply r " +
                    " WHERE p.`pk_userId`=s.`fk_userId` and s.`pk_postId`=r.`fk_postId` AND s.`pk_postId`=? ");
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                 post = new PostBean();
                post.setId(rs.getInt("pk_postId"));
                post.setPostName(rs.getString("t_name"));
                post.setPostContent(rs.getString("t_content"));
                ChannelBean channelBean = new ChannelBean();
                channelBean.setId(rs.getInt("fk_channelId"));
                post.setChannel(channelBean);
                String bir = rs.getString("t_date");
                post.setDate(bir!=null? LocalDate.parse(bir):null);
                UserBean user = new UserBean();
                user.setUserName(rs.getString("p"));
                post.setPostWriter(user);
                post.setReplyNum(rs.getInt("replyNum"));

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return post;
    }

    public static void main(String[] args) {
        PostDao dao = new PostDaoImpl();
        System.out.println(dao.findChannelById(1,1,null,null,null,null));
//        System.out.println(dao.findById(12));
//        System.out.println(dao.findByPostId(2));

    }
}
